﻿CREATE PROCEDURE [dbo].[weeklyHoursByDay]
(
  @userId INT,
  @endDate DATETIME
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @startDate DATETIME
SET @startDate = DateAdd(day,-7,@endDate)

SELECT
  Company,
  CAST(Activity AS VARCHAR(30)) AS Activity,
  ISNULL([Monday],0) AS [Monday],
  ISNULL([Tuesday],0) AS [Tuesday],
  ISNULL([Wednesday],0) AS [Wednesday],
  ISNULL([Thursday],0) AS [Thursday],
  ISNULL([Friday],0) AS [Friday],
  ISNULL([Saturday],0) AS [Saturday],
  ISNULL([Sunday],0) AS [Sunday],
  ISNULL([Monday],0) AS [Monday]
FROM
  (
  SELECT
    c.Name AS Company,
    CASE DATEPART(dw,tl.createDate)
      WHEN 1 THEN 'Sunday'
      WHEN 2 THEN 'Monday'
      WHEN 3 THEN 'Tuesday'
      WHEN 4 THEN 'Wednesday'
      WHEN 5 THEN 'Thursday'
      WHEN 6 THEN 'Friday'
      WHEN 7 THEN 'Saturday'
    END AS "WorkDay",
    at.name AS Activity,
    CASE WHEN SUM(tl.duration) > 0 
      THEN SUM(tl.duration) / 60
      ELSE 0 
    END AS HoursLogged
  FROM
    timeLog tl
    INNER JOIN activityType at ON at.activityTypeId = tl.activityTypeId
    INNER JOIN role r ON r.roleId = at.roleId
    INNER JOIN company c ON c.companyId = r.companyId
    INNER JOIN companyUser cu ON cu.companyId = c.companyId
  WHERE 
    tl.createDate BETWEEN @startDate AND @endDate
    AND
    cu.userId = @userId   
  GROUP BY
   c.name,
   DATEPART(dw,tl.createDate),
   at.name
  ) AS sourceTable
PIVOT
  (
  SUM(HoursLogged)
  FOR WorkDay IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
  ) AS pivotTable
  ORDER BY 1,2
  
END
RETURN